﻿IF  not exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[{0}](
	instance_id varchar(40) not null,
	definition_id varchar(40) not null,
	task_id varchar(40) not null,
	process_id varchar(40) null,
	parent_task_id varchar(40) null,
	version int not null,
	status int not null,
	start_date datetime not null,
	finish_date datetime null,
	last_modified datetime not null,
	task_data ntext null,
	scheduling_status int not null,
	task_type varchar(50) null,
	last_index_date datetime null
	CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
	(
		instance_id ASC
	)
)

create index IDX_{0}_definition_id_task_id on {0}(definition_id, task_id)
create index IDX_{0}_process_id on {0}(process_id)
create index IDX_{0}_parent_task_id on {0}(parent_task_id)
create index IDX_{0}_scheduling_status_status_start_date on {0}(scheduling_status, status, start_date)
create index IDX_{0}_task_type_status_start_date on {0}(task_type, status, start_date) include(instance_id)

END

IF  not exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NGTaskStatus]') AND type in (N'U'))
BEGIN

CREATE TABLE [dbo].[NGTaskStatus](
	[id] [int] NOT NULL,
	[name] [varchar](50) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)
)

insert into ngtaskstatus values(1, 'Enabled')
insert into ngtaskstatus values(2, 'Selected')
insert into ngtaskstatus values(3, 'Completed')
insert into ngtaskstatus values(4, 'Cancelled')
insert into ngtaskstatus values(5, 'Failed')

END

